set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;

with barscan_sitearrival as (
select
 waybill_no,
       scan_time,
       check_flag,
       scan_site,
       scan_site_code,
       to_date(scan_time) as dt
from (
         select waybill_no,
                scan_time,
                check_flag,
                scan_site,
                scan_site_code,
                row_number() over (partition by waybill_no,scan_site_code order by scan_time desc ) as rn
         from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
         where dt >= date_sub('{{ execution_date |  cst_ds }}', 15)
           and dt <= '{{ execution_date |  cst_ds }}'
           and scan_type = '到件扫描'
           and to_date(scan_time) >= date_sub('{{ execution_date |  cst_ds }}', 6)
           and to_date(scan_time) <= '{{ execution_date |  cst_ds }}'
     )tmp
where tmp.rn=1
),
oms_oms_waybill_incre as (
    select
       waybill_no,
      ordersource_name                                  --平台名字
from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
where dt>=date_sub('{{ execution_date |  cst_ds }}',30)
 and dt<='{{ execution_date |  cst_ds }}'
         )
,dwd_tab_barscan_other_base_dt_step1 as (
 select t.waybill_no,
        t1.agent_code,
        t1.agent_name,
        t1.provider_id,
        t1.provider_desc,
        t1.city_id,
        t1.city_desc,
        t.scan_site_code,
        t1.name,
        case when  b.ordersource_name='紫金山' then '紫金山'
             when  b.ordersource_name='逍遥峰' then '逍遥峰'
             when  b.ordersource_name='桃花岛' then '桃花岛'
             when  b.ordersource_name='七星潭' then '七星潭'
             when  b.ordersource_name='极地湾' then '极地湾'
             when  b.ordersource_name='有赞'   then '有赞'
             when  b.ordersource_name='麦田圈' then '麦田圈'
             when  b.ordersource_name='苏宁'   then '苏宁'
           else '其他' end as  ordersource_name,
        row_number() over (partition by t.waybill_no,t.scan_site_code order by t.scan_time desc ) as rn,
        to_date(t.scan_time) as dt
 from jms_dwd.dwd_tab_barscan_other_base_dt t
 left join jms_dim.dim_network_whole_massage  t1 on t.scan_site_code=t1.code
 left join oms_oms_waybill_incre b on t.waybill_no=b.waybill_no
 where t.dt >= date_sub('{{ execution_date |  cst_ds }}', 15)
   and t.dt <= '{{ execution_date |  cst_ds }}'
   and to_date(t.scan_time) >= date_sub('{{ execution_date |  cst_ds }}', 6)
   and to_date(t.scan_time) <= '{{ execution_date |  cst_ds }}'
   and t.scan_type='乡镇发件扫描'
having rn =1
)
,dwd_tab_barscan_other_base_dt as (
 select
      agent_code,                    --代理区code
      max(agent_name) as agent_name, --代理区名字
      provider_id,                   --省份id
      max(provider_desc) as provider_desc,  --省份
      city_id,                      --城市id
      max(city_desc) as city_desc,  --城市
      scan_site_code,               --网点code
      max(name) as name,            --网点名字
      ordersource_name,             --订单来源
      0 as waybill_num,
      0 as arrival_deliver_num,
      0 as after_deliver_num,
      count(1) as township_num,
      t.dt as date_time,
      t.dt
 from dwd_tab_barscan_other_base_dt_step1 t
 group by dt,scan_site_code,agent_code,ordersource_name,provider_id,city_id
)

insert overwrite table jms_dm.dm_network_arrival_union_deliver_sum_dt partition(dt)
--网点到出一体监控
select
  agent_code                    --代理区code
 ,max(agent_name) as agent_name --代理区名字
 ,provider_id                   --省份id
 ,max(provider_desc) as provider_desc  --省份
 ,city_id                      --城市id
 ,max(city_desc) as city_desc  --城市
 ,scan_site_code               --网点code
 ,max(name) as name            --网点名字
 ,ordersource_name             --订单来源
 ,sum(waybill_num) as waybill_num      --运单总量
 ,sum(arrival_deliver_num ) as arrival_deliver_num  --到出一体总量
 ,sum(after_deliver_num) as after_deliver_num    --稍后出仓总量
 ,dt as date_time --业务日期
 ,sum(township_num) as township_num        --乡镇发件量
 ,dt
from (
    select
          agent_code                    --代理区code
         ,max(agent_name) as agent_name --代理区名字
         ,provider_id                   --省份id
         ,max(provider_desc) as provider_desc  --省份
         ,city_id                      --城市id
         ,max(city_desc) as city_desc  --城市
         ,scan_site_code               --网点code
         ,max(name) as name            --网点名字
         ,ordersource_name             --订单来源
         ,count(1) as waybill_num      --运单总量
         ,sum(if(check_flag in (7,9,10,11,12,13),1,0)) as arrival_deliver_num  --到出一体总量
         ,sum(if(check_flag in (10,11),1,0)) as after_deliver_num   --稍后出仓总量
         ,0 as township_num
         ,dt as date_time --业务日期
         ,dt
    from (
         select
           c.agent_code,
           c.agent_name,
           c.provider_id,
           c.provider_desc,
           c.city_id,
           c.city_desc,
           a.scan_site_code,
           a.scan_time,
           c.name,
           a.waybill_no,
           a.check_flag,
          case when  b.ordersource_name='紫金山' then '紫金山'
               when  b.ordersource_name='逍遥峰' then '逍遥峰'
               when  b.ordersource_name='桃花岛' then '桃花岛'
               when  b.ordersource_name='七星潭' then '七星潭'
               when  b.ordersource_name='极地湾' then '极地湾'
               when  b.ordersource_name='有赞' then '有赞'
               when  b.ordersource_name='麦田圈' then '麦田圈'
               when  b.ordersource_name='苏宁' then '苏宁'
               else '其他' end as  ordersource_name,
         dt
    from barscan_sitearrival a
    left join oms_oms_waybill_incre b
    on a.waybill_no=b.waybill_no
    join (
        select code,
               name,
               agent_code,
               agent_name,
               provider_id,
               provider_desc,
               city_id,
               city_desc
        from jms_dim.dim_network_whole_massage
        where network_type=6
        ) c
    on a.scan_site_code=c.code
             )detail
    group by dt,scan_site_code,agent_code,ordersource_name,provider_id,city_id
    union all
    select
          t.agent_code,
          t.agent_name,
          t.provider_id,
          t.provider_desc,
          t.city_id,
          t.city_desc,
          t.scan_site_code,
          t.name,
          t.ordersource_name,
          t.waybill_num,
          t.arrival_deliver_num,
          t.after_deliver_num,
          t.township_num,
          t.date_time,
          t.dt
    from dwd_tab_barscan_other_base_dt t

) t

group by dt,scan_site_code,agent_code,ordersource_name,provider_id,city_id


distribute by 1
;
